# _*_ coding:utf-8 _*_
# @File  : daily_rank.py
# @Time  : 2021-06-28  15:22
# @Author: zizle

import os
import datetime
from fastapi import APIRouter, Query, Depends, Body, UploadFile, Form
import pandas as pd
from db import FAConnection
from interfaces.depends import admin_logged_require, verify_date
from spiders import futures_daily_rank
from status import r_status
from configs import APP_DIR

daily_rank_api = APIRouter()


# --------------------- 爬取/处理/保存 排名数据 --------------------


@daily_rank_api.post('/rank-file/', summary='上传排名文件')
async def save_daily_quotes_file(person: dict = Depends(admin_logged_require), ex: str = Query(...),
                                 date: datetime.datetime = Depends(verify_date), quotes_file: UploadFile = Form(...),
                                 variety: str = Form('')):
    if ex not in ['cffex', 'czce', 'dce', 'shfe']:
        return {'code': r_status.NOT_CONTENT, 'message': '上传失败,param `ex` error!'}
    suffix = os.path.splitext(quotes_file.filename)[1]
    if ex.lower() == 'cffex':
        temp_filepath = os.path.join(APP_DIR, f'temp/{ex}/rank/{date.strftime("%Y%m%d")}_{variety.upper()}{suffix}')
    else:
        temp_filepath = os.path.join(APP_DIR, f'temp/{ex}/rank/{date.strftime("%Y%m%d")}{suffix}')
    content = await quotes_file.read()  # 将文件保存到目标位置
    with open(temp_filepath, "wb") as fp:
        fp.write(content)
    await quotes_file.close()
    return {'code': r_status.CREATED_SUCCESS, 'message': '上传成功!'}


@daily_rank_api.get('/daily-rank/', summary='获取日持仓数据')
async def get_daily_quotes(date: str = Query(...), ex: str = Query(...)):
    try:
        quotes_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    ex = ex.upper()
    # 获取数据
    if ex == 'CZCE':
        spider = futures_daily_rank.CZCEDailyRank(parser_date=quotes_date)
    elif ex == 'DCE':
        spider = futures_daily_rank.DCEDailyRank(parser_date=quotes_date)
    elif ex == 'SHFE':
        spider = futures_daily_rank.SHFEDailyRank(parser_date=quotes_date)
    elif ex == 'CFFEX':
        spider = futures_daily_rank.CFFEXDailyRank(parser_date=quotes_date)
    else:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'ex validate error!', 'data': []}
    rep_data, msg = spider.get_daily_rank()
    # 处理数据
    df = pd.DataFrame(rep_data)
    data_str = quotes_date.strftime('%Y-%m-%d')
    df['rank_date'] = [data_str for _ in range(df.shape[0])]
    df.sort_values(by=['variety_en', 'contract', 'rank'], inplace=True)
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': f'获取{date}-{ex}的日持仓数据成功!', 'data': rep_data}


@daily_rank_api.post('/daily-rank/', summary='保存日持仓排名数据')
def save_daily_quotes(rank_data: list = Body(...)):
    if len(rank_data) < 1:
        return {'code': r_status.CREATED_SUCCESS, 'message': '没有发现可以保存的排名数据。', 'count': 0}
    creator = 1
    # 处理数据
    df = pd.DataFrame(rank_data)
    df['rank_ts'] = df['rank_date'].apply(lambda x: int(datetime.datetime.strptime(x, '%Y-%m-%d').timestamp()))
    df['creator'] = 1
    df.fillna('-', inplace=True)
    df['trade_company'] = df['trade_company'].apply(lambda x: x.strip())
    df['long_position_company'] = df['long_position_company'].apply(lambda x: x.strip())
    df['short_position_company'] = df['short_position_company'].apply(lambda x: x.strip())
    del df['rank_date']
    save_data = df.to_dict(orient='records')
    first_data = save_data[0]
    db = FAConnection(conn_name='Save Futures Daily Rank')
    # 先查询一下数据是否存在吧
    exist_sql = "SELECT id FROM dat_futures_daily_rank WHERE rank_ts=%s AND variety_en=%s LIMIT 1;"
    exist = db.query(exist_sql, param=[first_data['rank_ts'], first_data['variety_en']], keep_conn=True, fetchone=True)[0]
    if exist:
        db.close()
        return {'code': r_status.CREATED_SUCCESS, 'message': '该日排名数据已存在，不用重复保存！', 'count': 0}
    # 保存数据
    save_sql = 'INSERT IGNORE INTO dat_futures_daily_rank (creator,rank_ts,variety_en,contract,' \
               'rank,trade_company,trade,trade_increase,' \
               'long_position_company,long_position,long_position_increase,' \
               'short_position_company,short_position,short_position_increase) ' \
               'VALUES (%(creator)s,%(rank_ts)s,%(variety_en)s,%(contract)s,' \
               '%(rank)s, %(trade_company)s,%(trade)s,%(trade_increase)s,' \
               '%(long_position_company)s,%(long_position)s,%(long_position_increase)s,' \
               '%(short_position_company)s,%(short_position)s,%(short_position_increase)s);'
    count, _ = db.insert(save_sql, param=save_data, many=True)
    return {'code': r_status.CREATED_SUCCESS, 'message': f'保存日持仓排名入库数量{count}！', 'count': count}


# --------------------- 维护 排名数据 --------------------

@daily_rank_api.get('/', summary='查询排名数据')
async def get_rank_data(date: str = Query(...)):
    try:
        rank_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    query_sql = 'SELECT id,rank_ts,variety_en,contract,rank,trade_company,trade,trade_increase,' \
                'long_position_company,long_position,long_position_increase,' \
                'short_position_company,short_position,short_position_increase ' \
                'FROM dat_futures_daily_rank ' \
                'WHERE rank_ts=%s;'
    db = FAConnection(conn_name='Query-Rank')
    records = db.query(query_sql, [int(rank_date.timestamp())])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '查询排名数据为空!', 'data': []}
    df['rank_date'] = df['rank_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询排名数据成功!', 'data': rep_data}

